use "S:\Projects\Oxford - Transfer Pricing\Data\CBT\wdi_select.dta" , clear
keep data436 data742 data439 year CountryName CountryCode data185 data171 data687
rename data742 logistics
rename data436 bribery
rename data439 misreport
rename data185 publicrating
rename data171 pubresource
rename data687 transinvest
count
tab year
sum
rename CountryCode alpha3code
merge m:1 alpha3code using "S:\Projects\Oxford - Transfer Pricing\Data\CBT\ISO_countrycode.dta"

drop if alpha3code=="WLD"| alpha3code=="UMC"| alpha3code=="SST"| alpha3code=="SSF"| ///
alpha3code=="SSA"| alpha3code=="PSS"| alpha3code=="OSS"| alpha3code=="OED"| ///
alpha3code=="OEC"| alpha3code=="NOC"| alpha3code=="NAC"| alpha3code=="MNA"| ///
alpha3code=="MIC"| alpha3code=="MEA"| alpha3code=="LMY"| alpha3code=="LMC"| ///
alpha3code=="LIC"| alpha3code=="LDC"| alpha3code=="LCN"| alpha3code=="LAC"| ///
alpha3code=="INX"| alpha3code=="HPC"| alpha3code=="HIC"| alpha3code=="FCS"| ///
alpha3code=="EUU"| alpha3code=="EMU"| alpha3code=="ECS"| alpha3code=="ECA"| ///
alpha3code=="EAS"| alpha3code=="EAP"| alpha3code=="CEB"| alpha3code=="ARB"

drop if _merge==2
tab _merge
drop country numeric alpha2code _merge
sum
foreach var in logistics bribery misreport publicrating pubresource transinvest{
bysort alpha3code: egen av_`var'=mean(`var')
}

label variable av_pubresource "CPIA equity of public resource using rating (1=low to 6=high)"
label variable av_publicrating "CPIA transparency, accountability, and corruption in the puclic sector rating (1=low)"
label variable av_bribery "Firms expected to give gifts in meetings with tax officials (% of firms)"
label variable av_misreport "Firms that do not report all sales for tax purposes (% of firms)"
label variable av_transinvest "Investment in transport with private participation (current US$)"
label variable av_logistics "Logistics performance: Quality of trade and transport_related infrastrure (1=low)"

keep CountryName alpha3code av_*
duplicates drop
duplicates report CountryName
replace alpha3code="AND" if alpha3code=="ADO"
replace alpha3code="COD" if alpha3code=="ZAR"
replace alpha3code="TLS" if alpha3code=="TMP"
replace alpha3code="ROU" if alpha3code=="ROM"
replace alpha3code="IMN" if alpha3code=="IMY"
save "S:\Projects\Oxford - Transfer Pricing\Data\CBT\wdi_cleaned.dta" , replace

use "${hmrc_datadir}\CBT\governance indicators", clear
sum
duplicates report alpha3code year
drop *_pct
foreach var in corruption gvteffect law stability regulatory voice governance{
bysort alpha3code: egen av_`var'=mean(`var'_est)
}

keep alpha3code av_*
duplicates drop
duplicates report alpha3code

label var av_corruption "average estimates of government corrpution"
label var av_gvteffect "average estimates of government effectiveness"
label var av_law "average estimates of law enforcenment"
label var av_stability "average estimates of political stability, terrorist activity etc"
label var av_regulatory "average estimates of government regulation"
label var av_voice "average estimates of free speech"
label var av_governance "average estimates of governance"

merge 1:1 alpha3code using "S:\Projects\Oxford - Transfer Pricing\Data\CBT\wdi_cleaned.dta" 
drop _merge CountryName
merge 1:m alpha3code using "${hmrc_datadir}\export_reg_final_ukmncs_fy"
drop if _merge==1
drop  _merge
drop *est *pct estimation_governance

foreach var of varlist av_* {
egen mean_`var'=mean(`var')
egen stddv_`var'=sd(`var')
gen sd_`var'=(`var'-mean_`var')/stddv_`var'
}
save "${hmrc_datadir}\export_reg_final_ukmncs_fy_wdi", replace
